package com.lx.chapter2.helper;

import com.lx.chapter2.utils.PropsUtil;

import org.apache.commons.collections4.MapUtils;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import lombok.extern.slf4j.Slf4j;

/**
 * 数据库操作助手类
 *
 * @author 段誉
 * @create 2019-03-22 14:55
 */
@Slf4j
public final class DatabaseHelper {

  private static final QueryRunner QUERY_RUNNER;

  private static final ThreadLocal<Connection> CONNECTION_HOLDER;

  private static final BasicDataSource DATA_SOURCE;

  //初始化数据库链接配置
  static {
    QUERY_RUNNER = new QueryRunner();

    CONNECTION_HOLDER = new ThreadLocal<>();

    Properties conf = PropsUtil.loadProps("config.properties");
    String driver = conf.getProperty("jdbc.driver");
    String url = conf.getProperty("jdbc.url");
    String username = conf.getProperty("jdbc.username");
    String password = conf.getProperty("jdbc.password");

    DATA_SOURCE = new BasicDataSource();
    DATA_SOURCE.setDriverClassName(driver);
    DATA_SOURCE.setUrl(url);
    DATA_SOURCE.setUsername(username);
    DATA_SOURCE.setPassword(password);
  }

  /**
   * 获取数据库连接
   */
  public static Connection getConnection() {
    //获取Connection时，先从ThreadLocal中寻找如果不存在则创建
    Connection conn = CONNECTION_HOLDER.get();
    if (conn == null) {
      try {
        conn = DATA_SOURCE.getConnection();
      } catch (SQLException e) {
        log.error("get connection failure", e);
        throw new RuntimeException(e);
      } finally {
        //将新创建的connection放入ThreadLocal中
        CONNECTION_HOLDER.set(conn);
      }
    }
    return conn;
  }

  /**
   * 查询实体列表
   */
  public static <T> List<T> queryEntityList(Class<T> entityClass, String sql, Object... params) {
    List<T> entityList;
    try {
      Connection conn = getConnection();
      //使用DbUtils提供的QueryRunner对象可以面向实体(Entity)进行查询。
      //DbUtils首先执行SQL语句并返回一个ResultSet，随后通过反射会去创建并初始化实体对象。
      //由于我们需要返回的是List，因此可以使用BeanListHandler
      entityList = QUERY_RUNNER.query(conn, sql, new BeanListHandler<>(entityClass), params);
    } catch (SQLException e) {
      log.error("query entity list failure", e);
      throw new RuntimeException(e);
    }
    return entityList;
  }

  /**
   * 查询单个实体
   */
  public static <T> T queryEntity(Class<T> entityClass, String sql, Object... params) {
    T entity;
    try {
      Connection conn = getConnection();
      entity = QUERY_RUNNER.query(conn, sql, new BeanHandler<T>(entityClass), params);
    } catch (SQLException e) {
      log.error("query entity failure", e);
      throw new RuntimeException(e);
    }
    return entity;
  }

  /**
   * 通用执行查询语句(可能为多表关联查询)
   */
  public static List<Map<String, Object>> executeQuery(String sql, Object... params) {
    List<Map<String, Object>> result;
    try {
      Connection conn = getConnection();
      result = QUERY_RUNNER.query(conn, sql, new MapListHandler(), params);
    } catch (SQLException e) {
      log.error("execute query failure", e);
      throw new RuntimeException(e);
    }
    return result;
  }

  /**
   * 通用执行更新语句（包括update、insert、delete）
   */
  public static int executeUpdate(String sql, Object... params) {
    int rows = 0;
    try {
      Connection conn = getConnection();
      rows = QUERY_RUNNER.update(conn, sql, params);
    } catch (SQLException e) {
      log.error("execute update failure", e);
    }
    return rows;
  }

  /**
   * 插入实体
   */
  public static <T> boolean insertEntity(Class<T> entityClass, Map<String, Object> fieldMap) {
    if (MapUtils.isEmpty(fieldMap)) {
      log.error("can not insert entity: fieldMap is empty");
      return false;
    }

    String sql = "INSERT INTO " + getTableName(entityClass);
    StringBuilder columns = new StringBuilder("(");
    StringBuilder values = new StringBuilder("(");
    for (String fieldName : fieldMap.keySet()) {
      columns.append(fieldName).append(", ");
      values.append("?, ");
    }
    columns.replace(columns.lastIndexOf(","), columns.length(), ")");
    values.replace(values.lastIndexOf(","), values.length(), ")");
    sql += columns + " VALUES " + values;
    Object[] params = fieldMap.values().toArray();
    return executeUpdate(sql, params) == 1;
  }

  /**
   * 更新实体类
   */
  /**
   * 删除实体类
   */
  public static <T> boolean updateEntity(Class<T> entityClass, long id,
                                         Map<String, Object> fieldMap) {
    if (MapUtils.isEmpty(fieldMap)) {
      log.error("can not update entity: fieldMap is empty");
      return false;
    }

    String sql = "UPDATE " + getTableName(entityClass) + " SET ";
    StringBuilder columns = new StringBuilder();
    for (String fieldName: fieldMap.keySet()) {
      columns.append(fieldName).append("=?, ");
    }
    sql += columns.substring(0, columns.lastIndexOf(",")) + " WHERE id=?";
    List<Object> paramList = new ArrayList<>();
    paramList.addAll(fieldMap.values());
    paramList.add(id);
    Object[] papams = paramList.toArray();
    return executeUpdate(sql, papams) == 1;
  }

  /**
   * 删除实体
   */
  public static <T> boolean deleteEntity(Class<T> entityClass, long id) {
    String sql = "DELETE FROM " + getTableName(entityClass) + " WHERE id=?";
    return executeUpdate(sql, id) == 1;
  }

  /**
   * 获取添加的实体类所有字段
   */
  private static String getTableName(Class<?> entityClass) {
    return entityClass.getSimpleName();
  }

  /**
   * 执行sql文件
   */
  public static void executeSqlFile(String filePath) {
    InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(filePath);
    BufferedReader reader = new BufferedReader(new InputStreamReader(is));
    try {
      String sql;
      while ((sql = reader.readLine()) != null) {
        executeUpdate(sql);
      }
    } catch (IOException e) {
      log.error("execute sql file failure", e);
      throw new RuntimeException(e);
    }
  }
}
